<?php
class Shinkensaku_Model_SelChoumei extends Shinkensaku_Model_Base{

	/**
	 * 表示用変換
	 * @param $selChoumei
	 * @param $params
	 */
	public function convertData($selChoumei, $params)
	{
    	$DispList = array();
    	$indexNo = 0;
        $beforeCity = null;

        foreach ($selChoumei as $ChoumeiData)
        {
            $city = $ChoumeiData['SHIKUCHOUSON'];

            // 市区町村が異なる場合
            if ($beforeCity != $city)
            {
            	$DispList["$city"]['SHIKUCHOUSON_CD'] = $ChoumeiData['SHIKUCHOUSON_CD'];
            	$DispList["$city"]['SHIKUCHOUSON'] = $city;
            	$DispList["$city"]['SHIKUCHOUSON_HEYA_CNT'] = $ChoumeiData['SHIKUCHOUSON_HEYA_CNT'];
                $DispList["$city"]['townList'] = array();
            }

            // チェック済みにするか判定
            if (!empty($params['ReTownCd']) && in_array($ChoumeiData['CHOUMEI_CD'], $params['ReTownCd']))
            {
            	$ChoumeiData['checked'] = "checked='checked'";
            }

            $DispList["$city"]['townList'][] = $ChoumeiData;

            $beforeCity = $city;
        }

        return $DispList;
	}

    /**
     * sql文生成(町名集計テーブル更新処理)
     * @param $params
     * @param $db_params
     * @return sql文
     */
	public function choumeiKoushin($params)
	{
    	$strWhere1 = '';
    	$strWhere2 = '';
    	$strWhere7 = '';
		$strWhere8 = '';
		$strWhere9 = '';

        // 都道府県・市区町村コードリスト作成
    	for ($i = 0; $i < count($params['CityCd']); $i++)
    	{
	        $PrefCd = substr($params['CityCd'][$i], 0, 2);
	        $CityCd = substr($params['CityCd'][$i], 2);

	        if ($i == 0)
	        {
	        	$strWhere1 = "(T55_TODOUFUKEN_CD = '{$PrefCd}' AND T55_SHIKUCHOUSON_CD = '{$CityCd}')";
	        	$strWhere2 = "(M01_TODOUFUKEN_CD = '{$PrefCd}' AND M01_SHIKUCHOUSON_CD = '{$CityCd}')";
	        }
	        else
	        {
	        	$strWhere1 .= "OR (T55_TODOUFUKEN_CD = '{$PrefCd}' AND T55_SHIKUCHOUSON_CD = '{$CityCd}')";
	        	$strWhere2 .= "OR (M01_TODOUFUKEN_CD = '{$PrefCd}' AND M01_SHIKUCHOUSON_CD = '{$CityCd}')";
	        }
    	}
    	$strWhere1 .= "AND T55_DATA_KUBUN = 0 ";

    	// クエリ作成（集計TBLに確認すべきﾃﾞｰﾀの存在確認）
$sql = <<< DOC_END

		SELECT DISTINCT
			COUNT(*) AS CNT
		FROM

			(SELECT
				T55_TODOUFUKEN_CD,
				T55_SHIKUCHOUSON_CD
			FROM T55CHOUMEI_SUM
			WHERE {$strWhere1}),

			(SELECT DISTINCT
				M01_TODOUFUKEN_CD,
				M01_SHIKUCHOUSON_CD
			FROM M01JYUUSHO
			WHERE {$strWhere2})

		WHERE M01_TODOUFUKEN_CD = T55_TODOUFUKEN_CD(+)
		AND M01_SHIKUCHOUSON_CD = T55_SHIKUCHOUSON_CD(+)
		AND T55_SHIKUCHOUSON_CD IS NULL
		GROUP BY M01_TODOUFUKEN_CD, M01_SHIKUCHOUSON_CD

DOC_END;

		$result_count = $this->dbGetList($sql, $db_params);

		// データが存在した場合
		if (count($result_count) > 0)
		{
    		// クエリ作成（挿入条件の都道府県取得）
$sql = <<< DOC_END

			SELECT DISTINCT
				M01_TODOUFUKEN_CD,
				M01_SHIKUCHOUSON_CD
			FROM

				(SELECT
					T55_TODOUFUKEN_CD,
					T55_SHIKUCHOUSON_CD
				FROM T55CHOUMEI_SUM
				WHERE
					{$strWhere1}),

				(SELECT DISTINCT
					M01_TODOUFUKEN_CD,
					M01_SHIKUCHOUSON_CD
				FROM M01JYUUSHO
				WHERE {$strWhere2})

			WHERE M01_TODOUFUKEN_CD = T55_TODOUFUKEN_CD(+)
			AND M01_SHIKUCHOUSON_CD = T55_SHIKUCHOUSON_CD(+)
			AND T55_SHIKUCHOUSON_CD IS NULL

DOC_END;
			$result_sel_for_ins = $this->dbGetList($sql, $db_params);

			for ($i = 0; $i < count($result_sel_for_ins); $i++)
			{
				if ($i == 0)
				{
					$strWhere7 = " (T03_TODOUFUKEN_CD = '{$result_sel_for_ins[$i]['M01_TODOUFUKEN_CD']}' AND T03_SHIKUCHOUSON_CD = '{$result_sel_for_ins[$i]['M01_SHIKUCHOUSON_CD']}')";
				}
				else
				{
					$strWhere7 .= " OR (T03_TODOUFUKEN_CD = '{$result_sel_for_ins[$i]['M01_TODOUFUKEN_CD']}' AND T03_SHIKUCHOUSON_CD = '{$result_sel_for_ins[$i]['M01_SHIKUCHOUSON_CD']}')";
				}
			}
    		// クエリ作成（ﾃﾞｰﾀ挿入）
$sql = <<< DOC_END
			INSERT INTO T55CHOUMEI_SUM
			(SELECT
				T03_TODOUFUKEN_CD,
				T03_SHIKUCHOUSON_CD,
				T03_CHOUMEI_CD,
				T03_CHOUMEI,
				COUNT(T03_HEYA_CD)AS EKI_HEYA_CNT,
				SYSDATE,
				0
			FROM T03WEB_HEYA
			WHERE ({$strWhere7})
			GROUP BY T03_TODOUFUKEN_CD, T03_SHIKUCHOUSON_CD, T03_CHOUMEI_CD, T03_CHOUMEI
			)
DOC_END;

	        $db = $this->getDbConnection();
	        $this->startTransaction($db);
	        try {
	            $this->dbExecQuery($sql, $db_params, $db);
	            $this->commitTransaction($db);
	        } catch (Exception $e) {
	            $this->rollbackTransaction($db);
	        }
	        $db->closeConnection();
		}

    		// クエリ作成（削除対象用ﾃﾞｰﾀ抽出）
$sql = <<< DOC_END
			SELECT DISTINCT
				T55_TODOUFUKEN_CD,
				T55_SHIKUCHOUSON_CD,
				T55_KOUSHIN
			FROM T55CHOUMEI_SUM,

				(SELECT DISTINCT
					M01_TODOUFUKEN_CD,
					M01_SHIKUCHOUSON_CD
				FROM M01JYUUSHO
				WHERE
					{$strWhere2}
				GROUP BY M01_TODOUFUKEN_CD, M01_SHIKUCHOUSON_CD
				)

			WHERE T55_KOUSHIN < SYSDATE - 30 / 1440
			AND M01_TODOUFUKEN_CD = T55_TODOUFUKEN_CD
			AND M01_SHIKUCHOUSON_CD = T55_SHIKUCHOUSON_CD
			AND T55_DATA_KUBUN = 0
DOC_END;

		$result_sel_for_del = $this->dbGetList($sql, $db_params);

		// データが存在した場合
		if (count($result_sel_for_del) > 0)
		{
			for ($i = 0; $i < count($result_sel_for_del); $i++)
			{
				if ($i == 0)
				{
					$strWhere8 = "(T55_TODOUFUKEN_CD = '{$result_sel_for_del[$i]['T55_TODOUFUKEN_CD']}' AND T55_SHIKUCHOUSON_CD = '{$result_sel_for_del[$i]['T55_SHIKUCHOUSON_CD']}') ";
					$strWhere9 = "(T03_TODOUFUKEN_CD = '{$result_sel_for_del[$i]['T55_TODOUFUKEN_CD']}' AND T03_SHIKUCHOUSON_CD = '{$result_sel_for_del[$i]['T55_SHIKUCHOUSON_CD']}') ";
				}
				else
				{
					$strWhere8 .= "OR (T55_TODOUFUKEN_CD = '{$result_sel_for_del[$i]['T55_TODOUFUKEN_CD']}' AND T55_SHIKUCHOUSON_CD = '{$result_sel_for_del[$i]['T55_SHIKUCHOUSON_CD']}') ";
					$strWhere9 .= "OR (T03_TODOUFUKEN_CD = '{$result_sel_for_del[$i]['T55_TODOUFUKEN_CD']}' AND T03_SHIKUCHOUSON_CD = '{$result_sel_for_del[$i]['T55_SHIKUCHOUSON_CD']}') ";
				}
			}
			$strWhere8 .= "AND T55_DATA_KUBUN = 0 ";


    		// クエリ作成（ﾃﾞｰﾀ削除）
$sql = <<< DOC_END
			DELETE FROM T55CHOUMEI_SUM
			WHERE
				{$strWhere8}
DOC_END;

	        $db = $this->getDbConnection();
	        $this->startTransaction($db);
	        try {
	            $this->dbExecQuery($sql, $db_params, $db);
	            $this->commitTransaction($db);
	        } catch (Exception $e) {
	            $this->rollbackTransaction($db);
	        }
	        $db->closeConnection();

			// クエリ作成（集計TBLﾃﾞｰﾀ挿入）
$sql = <<< DOC_END

			INSERT INTO T55CHOUMEI_SUM
			(
				SELECT
					T03_TODOUFUKEN_CD,
					T03_SHIKUCHOUSON_CD,
					T03_CHOUMEI_CD,
					T03_CHOUMEI,
					COUNT(T03_HEYA_CD)  AS EKI_HEYA_CNT,
					SYSDATE,
					0
				FROM T03WEB_HEYA
				WHERE ({$strWhere9})
				GROUP BY T03_TODOUFUKEN_CD, T03_SHIKUCHOUSON_CD, T03_CHOUMEI_CD, T03_CHOUMEI
				)

DOC_END;

	        $db = $this->getDbConnection();
	        $this->startTransaction($db);
	        try {
	            $this->dbExecQuery($sql, $db_params, $db);
	            $this->commitTransaction($db);
	        } catch (Exception $e) {
	            $this->rollbackTransaction($db);
	        }
	        $db->closeConnection();
		}
	}

    /**
     * sql文生成（OemCdなし）
     * @param $params
     * @param $db_params
     * @return sql文
     */
	protected function generateSql($params, &$db_params)
    {
    	$strWhere1 ='';
    	$strWhere2 ='';

        // 都道府県・市区町村コードリスト作成
    	for ($i = 0; $i < count($params['CityCd']); $i++)
        {
	        $PrefCd = substr($params['CityCd'][$i], 0, 2);
	        $CityCd = substr($params['CityCd'][$i], 2);

	        if ($i == 0)
	        {
	        	$strWhere1 = "(T55_TODOUFUKEN_CD = '{$PrefCd}' AND T55_SHIKUCHOUSON_CD = '{$CityCd}')";
	        	$strWhere2 = "(T57_TODOUFUKEN_CD = '{$PrefCd}' AND T57_ENSEN_SHIKUCHOUSON_CD = '{$CityCd}' AND T57_ENSEN_SHIKUCHOUSON_FLAG = 1)";
	        }
	        else
	        {
	        	$strWhere1 .= "OR (T55_TODOUFUKEN_CD = '{$PrefCd}' AND T55_SHIKUCHOUSON_CD = '{$CityCd}')";
	        	$strWhere2 .= "OR (T57_TODOUFUKEN_CD = '{$PrefCd}' AND T57_ENSEN_SHIKUCHOUSON_CD = '{$CityCd}' AND T57_ENSEN_SHIKUCHOUSON_FLAG = 1)";
	        }
        }

$sql = <<< DOC_END

	SELECT
		T1.T55_CHOUMEI_CNT AS REC_CNT,
		T1.T55_TODOUFUKEN_CD || T1.T55_SHIKUCHOUSON_CD AS SHIKUCHOUSON_CD,
		T1.M43_TODOUFUKEN || T2.T57_ENSEN_SHIKUCHOUSON_NAME AS SHIKUCHOUSON,
		T1.T55_TODOUFUKEN_CD || T1.T55_SHIKUCHOUSON_CD || T1.T55_CHOUMEI_CD AS CHOUMEI_CD,
		T1.T55_CHOUMEI AS CHOUMEI,
		T2.SHIKUCHOUSON_HEYA_CNT
	FROM

		(SELECT
			T55_TODOUFUKEN_CD,
			T55_SHIKUCHOUSON_CD,
			T55_CHOUMEI_CD,
			M43_TODOUFUKEN,
			T55_CHOUMEI,
			T55_CHOUMEI_CNT
		FROM T55CHOUMEI_SUM, M43TODOUFUKEN
		WHERE ( {$strWhere1})
		AND T55_DATA_KUBUN = 0
		AND T55_TODOUFUKEN_CD = M43_TODOUFUKEN_CD
		) T1,

		(SELECT
			T57_ENSEN_SHIKUCHOUSON_CNT AS SHIKUCHOUSON_HEYA_CNT,
			T57_TODOUFUKEN_CD,
			T57_ENSEN_SHIKUCHOUSON_CD,
			T57_ENSEN_SHIKUCHOUSON_NAME
		FROM T57ENSEN_SHIKUCHOUSON_SUM
		WHERE ( {$strWhere2})
		AND T57_DATA_KUBUN = 0
		) T2

	WHERE T1.T55_TODOUFUKEN_CD = T2.T57_TODOUFUKEN_CD
	AND T1.T55_SHIKUCHOUSON_CD = T2.T57_ENSEN_SHIKUCHOUSON_CD
	AND T1.T55_CHOUMEI_CNT <> 0

	GROUP BY T1.T55_CHOUMEI_CNT,
			 T1.T55_TODOUFUKEN_CD,
			 T1.T55_SHIKUCHOUSON_CD,
			 T1.M43_TODOUFUKEN,
			 T2.T57_ENSEN_SHIKUCHOUSON_NAME,
			 T1.T55_CHOUMEI_CD,
			 T1.T55_CHOUMEI,
			 T2.SHIKUCHOUSON_HEYA_CNT
	ORDER BY SHIKUCHOUSON_CD ASC, CHOUMEI_CD ASC

DOC_END;
        return $sql;
    }

    /**
     * sql文生成（OemCdあり）
     * @param $params
     * @param $db_params
     * @return sql文
     */
	protected function generateOemSql($params, &$db_params)
    {
        $this->strWhere1 = '';

        // OemCdに基づく条件文を生成
    	$strWhereOemM05 = $this->generateCondOem($params);

        // 市区町村コードに基づく条件文を生成
        foreach (array_filter($params['CityCd']) as $CityCd)
        {
    		$tmpTodohukenCd = substr($CityCd, 0, 2);
    		$tmpCityCd = substr($CityCd, 2, 3);

    		// 初回以外
    		if (strcmp($this->strWhere1, '') != 0) $this->strWhere1 .= ' OR ';
			// 条件文を追加
    		$this->strWhere1 .= " (T03_TODOUFUKEN_CD = '{$tmpTodohukenCd}' AND T03_SHIKUCHOUSON_CD = '{$tmpCityCd}') ";
        }

        $sql .= "";
		$sql .= " SELECT";
		$sql .= "         NVL(T.REC_CNT, 0) AS REC_CNT";
		$sql .= "         ,T.T03_TODOUFUKEN_CD || T.T03_SHIKUCHOUSON_CD AS SHIKUCHOUSON_CD";
		$sql .= "         ,A.M01_TODOUFUKEN || A.M01_SHIKUCHOUSON AS SHIKUCHOUSON";
		$sql .= "         ,T.T03_TODOUFUKEN_CD || T.T03_SHIKUCHOUSON_CD || T.T03_CHOUMEI_CD AS CHOUMEI_CD";
		$sql .= "         ,A.M01_CHOUMEI AS CHOUMEI";
		$sql .= "         ,T.SHIKUCHOUSON_HEYA_CNT";
		$sql .= "     FROM";
		$sql .= "         M01JYUUSHO A";
		$sql .= "         ,(";
		$sql .= "             SELECT";
		$sql .= "                     T1.T03_TODOUFUKEN_CD";
		$sql .= "                     ,T1.T03_SHIKUCHOUSON_CD";
		$sql .= "                     ,T1.T03_CHOUMEI_CD";
		$sql .= "                     ,NVL(T1.REC_CNT, 0) AS REC_CNT";
		$sql .= "                     ,T2.REC_CNT AS SHIKUCHOUSON_HEYA_CNT";
		$sql .= "                 FROM";
		$sql .= "                     (";
		$sql .= "                         SELECT";
		$sql .= "                                 T03_TODOUFUKEN_CD";
		$sql .= "                                 ,T03_SHIKUCHOUSON_CD";
		$sql .= "                                 ,T03_CHOUMEI_CD";
		$sql .= "                                 ,COUNT(*) AS REC_CNT";
		$sql .= "                             FROM";
		$sql .= "                                 (";
		$sql .= "                                     SELECT";
		$sql .= "                                             T03_TODOUFUKEN_CD";
		$sql .= "                                             ,T03_SHIKUCHOUSON_CD";
		$sql .= "                                             ,T03_CHOUMEI_CD";
		$sql .= "                                             ,T03_ORIGINAL_TATEMONO_CD01";
		$sql .= "                                             ,T03_ORIGINAL_HEYA_CD01";
		$sql .= "                                             ,T03_KASHIDASHI_KUBUN";
		$sql .= "                                         FROM";
		$sql .= "                                             T03WEB_HEYA";
		$sql .= "                                             ,M05TENPO_INFO";
		$sql .= "                                         WHERE";
		$sql .= "                                             (".$this->strWhere1.")";
		$sql .= "                                             AND M05_TENPO_CD = T03_TENPO_CD";
		$sql .=                                               $strWhereOemM05;
		if(!empty($params['shikireizero']) && $params['shikireizero'] == "on"){
			$sql .= "                                         AND T03_KASHIDASHI_KUBUN = 1 ";
		}
		$sql .= "                                 )";
		$sql .= "                             GROUP BY";
		$sql .= "                                 T03_TODOUFUKEN_CD";
		$sql .= "                                 ,T03_SHIKUCHOUSON_CD";
		$sql .= "                                 ,T03_CHOUMEI_CD";
		$sql .= "                     ) T1";
		$sql .= "                     ,(";
		$sql .= "                         SELECT";
		$sql .= "                                 T03_TODOUFUKEN_CD";
		$sql .= "                                 ,T03_SHIKUCHOUSON_CD";
		$sql .= "                                 ,COUNT(*) AS REC_CNT";
		$sql .= "                             FROM";
		$sql .= "                                 (";
		$sql .= "                                     SELECT";
		$sql .= "                                             T03_TODOUFUKEN_CD";
		$sql .= "                                             ,T03_SHIKUCHOUSON_CD";
		$sql .= "                                             ,T03_ORIGINAL_TATEMONO_CD01";
		$sql .= "                                             ,T03_ORIGINAL_HEYA_CD01";
		$sql .= "                                             ,T03_KASHIDASHI_KUBUN";
		$sql .= "                                         FROM";
		$sql .= "                                             T03WEB_HEYA";
		$sql .= "                                             ,M05TENPO_INFO";
		$sql .= "                                         WHERE";
		$sql .= "                                             (".$this->strWhere1.")";
		$sql .= "                                             AND M05_TENPO_CD = T03_TENPO_CD";
		$sql .=                                               $strWhereOemM05;
		if(!empty($params['shikireizero']) && $params['shikireizero'] == "on"){
			$sql .= "                                         AND T03_KASHIDASHI_KUBUN = 1 ";
		}
		$sql .= "                                 )";
		$sql .= "                             GROUP BY";
		$sql .= "                                 T03_TODOUFUKEN_CD";
		$sql .= "                                 ,T03_SHIKUCHOUSON_CD";
		$sql .= "                     ) T2";
		$sql .= "                 WHERE";
		$sql .= "                     T1.T03_TODOUFUKEN_CD = T2.T03_TODOUFUKEN_CD";
		$sql .= "                     AND T1.T03_SHIKUCHOUSON_CD = T2.T03_SHIKUCHOUSON_CD";
		$sql .= "         ) T";
		$sql .= "     WHERE";
		$sql .= "         T.T03_TODOUFUKEN_CD = A.M01_TODOUFUKEN_CD";
		$sql .= "         AND T.T03_SHIKUCHOUSON_CD = A.M01_SHIKUCHOUSON_CD";
		$sql .= "         AND T.T03_CHOUMEI_CD = A.M01_CHOUMEI_CD";
		$sql .= "         AND T.REC_CNT <> 0";
		$sql .= "     GROUP BY";
		$sql .= "         T.T03_TODOUFUKEN_CD || T.T03_SHIKUCHOUSON_CD";
		$sql .= "         ,A.M01_TODOUFUKEN || A.M01_SHIKUCHOUSON";
		$sql .= "         ,T.T03_TODOUFUKEN_CD || T.T03_SHIKUCHOUSON_CD || T.T03_CHOUMEI_CD";
		$sql .= "         ,A.M01_CHOUMEI";
		$sql .= "         ,T.SHIKUCHOUSON_HEYA_CNT";
		$sql .= "         ,T.REC_CNT";
		$sql .= "     ORDER BY";
		$sql .= "         T.T03_TODOUFUKEN_CD || T.T03_SHIKUCHOUSON_CD ASC";
		$sql .= "         ,T.T03_TODOUFUKEN_CD || T.T03_SHIKUCHOUSON_CD || T.T03_CHOUMEI_CD ASC";

		return $sql;
    }
}
?>